﻿using DuiBa;
using DuiBa.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Top.Core;
using Top.Data;
using Top.PointStore.Business.Models;
using Top.PointStore.Business.Models.DuibaModel;

namespace Top.PointStore.Business
{
    public static class DuibaOrderManager
    {
        public static async Task<int> Add(DuibaOrder order,DbTour db)
        {
            if (order == null) return 0;
            const string sql =
@"insert into Duibaorder (State,Description,DuibaOrderNum,Other,CreateTime,Credits,UserToken,ProductType,AuditStatus,AuditTime)
                values({0:State},
                {0:Description},
                {0:DuibaOrderNum},
                {0:Other},
                {0:CreateTime},
                {0:Credits},
                {0:UserToken},
                {0:ProductType},
                {0:AuditStatus},
                {0:AuditTime});
                select LAST_INSERT_ID();";
            var rel=db.Sql(sql, order);
            return await rel.ExecuteScalar<int>();
        }
        /// <summary>
        /// 扣积分
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public static async Task<int> MinusPoints(DuibaOrder order)
        {
            T_PointMain_new pointMain = new T_PointMain_new();
            bool isExist = MemoryTable.TB_PointMain_new.ContainsKey(order.UserToken.ToUpper());
            if (isExist)
            {
                pointMain = MemoryTable.TB_PointMain_new[order.UserToken.ToUpper()];
            }
            else
            {
                pointMain = await PointManager.Get(order.UserToken);
            }
            if (pointMain == null) return -1;//用户不存在
            int orderId = 0;
            if(await Exist(order.DuibaOrderNum))
            {
                return -3;//订单已存在
            }
            using (var db = new DbTour())
            {
                orderId = await Add(order, db);
                if(orderId>0)
                {
                    var rel = await PointManager.UpdateUserPoint(order.UserToken, order.Credits, "duiba.point." + order.ProductType, orderId, "SUBTRACT", db);
                    if(rel<=0)
                    {
                        await UpdateState(orderId, DuibaOrderEnum.Fail,db);
                        return -2;//积分扣除失败
                    }
                }
            }
            return orderId;
        }
        public static async Task<bool> UpdateState(int kid,DuibaOrderEnum state)
        {
            using(DbTour db=new DbTour())
            {
                return await UpdateState(kid, state, db);
            }
        }
        public static async Task<bool> UpdateState(int kid,DuibaOrderEnum state,DbTour db)
        {
            const string sql = @"update Duibaorder set State={0} where Kid={1}";
            return await db.Sql(sql, state, kid).ExecuteNonQuery() > 0;
        }
        public static async Task<bool> Exist(string ordernum)
        {
            const string sql = @"select count(1) from Duibaorder where DuibaOrderNum={0}";
            using(DbTour db=new DbTour())
            {
                return await db.Sql(sql, ordernum).ExecuteScalar<int>()>0;
            }
        }
        public static async Task<DuibaOrder> Get(string ordernum)
        {
            const string sql = @"select * from Duibaorder where DuibaOrderNum={0}";
            using (DbTour db = new DbTour())
            {
                return await db.Sql(sql, ordernum).FirstOrDefault<DuibaOrder>();
            }
        }
        /// <summary>
        /// 更新订单状态
        /// </summary>
        /// <param name="orderNum"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public static async Task<bool> UpdateState(string orderNum,DuibaOrderEnum state)
        {
            var order = await DuibaOrderManager.Get(orderNum);
            if(order==null)
            {
                return false;
            }
            if(order.State==DuibaOrderEnum.Waiting)
            {
                switch(state)
                {
                    case DuibaOrderEnum.Success:
                        return await UpdateState(order.Kid, state);
                    case DuibaOrderEnum.Fail:
                        using(DbTour db=new DbTour())
                        {
                            await PointManager.UpdateUserPoint(order.UserToken, order.Credits, "duiba.point.return", order.Kid, "REFUND", db);
                            return await UpdateState(order.Kid, state,db);
                        }
                    default:
                        return false;
                }
            }
            return false;
        }
        /// <summary>
        /// 获取超时订单
        /// </summary>
        /// <returns></returns>
        public static async Task<IList<DuibaOrder>> GetOutTimeOrder()
        {
            const string sql = @"SELECT * FROM duibaorder WHERE CreateTime<{0} AND State={1}";
            using(DbTour db=DataBaseContext.GetDataBase("reader"))
            {
                var rel = db.Sql(sql, DateTime.Now.AddHours(-1), DuibaOrderEnum.Waiting);
                return await rel.ToList<DuibaOrder>();;
            }
        }
        //获取订单
        public static async Task<PagedList<DuibaAuditInfo>> GetPage(Pager pager, string orderNum, int state, int city, string type, DateTime start, DateTime end, string phone)
        {
            using (var db = DataBaseContext.GetDataBase("reader"))
            {
                var rel = GetListSql(db, orderNum, state, city, type, start, end, phone);
                return await rel.ToPagedList<DuibaAuditInfo>(pager.StartNumber, pager.EndNumber, "a.CreateTime desc");;
            }
        }
        /// <summary>
        /// 审核订单
        /// </summary>
        /// <param name="orderNum"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static async Task<bool> UpdateAuditStatus(string orderNum,DuibaOrderAuditStatus status)
        {
            var orderArr = orderNum.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string sql = @"update Duibaorder set AuditStatus={0},AuditTime={1} where DuibaOrderNum IN ";
            var request=orderArr.Select(u => "'" +u+ "'").ToArray();
            var orderStr = string.Join(",", request);
            sql = sql + "(" + orderStr + ")";
            using (var db = DataBaseContext.GetDataBase("write"))
            {
                switch (status)
                {
                    case DuibaOrderAuditStatus.Approved:
                        if(DuiBaSDK.UpdateAuditStatus(orderArr, null))
                        {
                            return await db.Sql(sql, status, DateTime.Now).ExecuteNonQuery()>0;
                        }
                        break;
                    case DuibaOrderAuditStatus.NotApproved:
                        if(DuiBaSDK.UpdateAuditStatus(null, orderArr))
                        {
                            return await db.Sql(sql, status, DateTime.Now).ExecuteNonQuery() > 0;
                        }
                        break;
                    default:
                        return await db.Sql(sql, status, DateTime.Now).ExecuteNonQuery()>0;
                }
            }
            return false;
        }
        //积分获取历史
        public async static Task<ViolationResponse> GetCreditHistory(string usertoken)
        {
            using (var db = DataBaseContext.GetDataBase("default"))
            {
                //1 用户注册时间
                string createTimeSql = @"
                    SELECT F_AddTime 
                    FROM t_oauthinfo 
                    WHERE F_UserToken={0}";
                DateTime addTime = await db.Sql(createTimeSql, usertoken).FirstOrDefault<DateTime>();
                if (addTime.Year == 1)
                {
                    return new ViolationResponse
                    {
                        success = false,
                        message = "找不到该用户"
                    };
                }
                //2 积分历史记录
                string detailSql = @"
                    SELECT detail.*,rule.F_PointAction
                    FROM t_pointmain_new AS main INNER JOIN t_pointdetail_new AS detail
                    ON main.Kid = detail.F_PointId
                    LEFT JOIN t_pointrule AS rule
                    ON detail.F_PointCode=rule.F_PointCode
                    WHERE main.F_UserToken = {0} AND detail.F_Point>0
                    ORDER BY detail.F_AddTime DESC
                    LIMIT 0,30";
                List<T_PointDetailExpand> details = await db.Sql(detailSql, usertoken).ToList<T_PointDetailExpand>();
                List<AddRecord> records = new List<AddRecord>();
                foreach (var detail in details)
                {
                    AddRecord record = new AddRecord
                    {
                        credits = detail.F_Point,
                        reason = detail.F_PointAction ?? "",
                        time = detail.F_AddTime
                    };
                    records.Add(record);
                }

                var response = new ViolationResponse
                {
                    success = true,
                    message = "OK",
                    create_date = addTime,
                    add_records = records
                };
                if (records.Count == 0)
                {
                    response.message = "该用户没有积分增加记录";
                }
                return response;
            }
        }

        //生成订单状态查询 URL
        public static string GetOrderStatusQueryUrl(string orderNum, string bizId)
        {
            string baseUrl = "http://www.duiba.com.cn/status/orderStatus";

            Dictionary<string, string> paramsDic = new Dictionary<string, string>();
            paramsDic.Add("appKey", DuiBaSDK.AppKey);
            paramsDic.Add("orderNum", orderNum);
            paramsDic.Add("bizId", bizId);
            paramsDic.Add("timestamp", DateTime.Now.ToTimesTamp().ToString());
            paramsDic.Add("sign", Commond.GenerateParamASCMD5(DuiBaSDK.AppSecret, paramsDic));

            return string.Format("{0}?{1}", baseUrl, Commond.GenerateUrlParams(paramsDic));
        }

        #region -=导出Excel=-

        //导出Excel
        public static async Task<string> Export(string orderNum, int state, int city, string type, DateTime start, DateTime end, string phone)
        {
            using (var db = DataBaseContext.GetDataBase("reader"))
            {
                DataTable result = await GetListSql(db, orderNum, state, city, type, start, end, phone).ExecuteDataTable();
                DataTable dt = CreateDataTable(result);
                string path = string.Empty;
                DataTableExcel(dt, string.Format("兑吧订单（{0}）.xls", DateTime.Now.ToString("yyyy-MM-dd-hhmmss")), out path);
                return path;
            }
        }

        //创建datatable
        private static DataTable CreateDataTable(DataTable tb)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("DuibaOrderNum", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("Description", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("Credits", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("State", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("ProductType", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("CreateTime", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("UserName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("Phone", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("CityName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("AdminName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("AdminPhone", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("AuditStatus", System.Type.GetType("System.String")));

            foreach (DataRow dr in tb.Rows)
            {
                string state = ConvertState(Convert.ToInt32(dr["State"].ToString()));
                string auditStatus = ConvertAuditStatus(Convert.ToInt32(dr["AuditStatus"].ToString()));
                string productType = ConvertProductType(dr["ProductType"].ToString());
                dt.Rows.Add(new string[] { dr["DuibaOrderNum"].ToString(), dr["Description"].ToString(), dr["Credits"].ToString(),
                    state,productType,dr["CreateTime"].ToString(),dr["UserName"].ToString(),dr["Phone"].ToString(),
                    dr["CityName"].ToString(),dr["AdminName"].ToString(),dr["AdminPhone"].ToString(),auditStatus });                 
            }
            return dt;
        }

        //转换订单状态
        private static string ConvertState(int state)
        {
            switch(state)
            {
                case 0: return "等待兑吧通知";
                case 1: return "兑换成功";
                case 2: return "兑换失败";
            }
            return "";
        }

        //转换审核状态
        private static string ConvertAuditStatus(int num)
        {
            switch (num)
            {
                case 0: return "未审核";
                case 1: return "审核通过";
                case 2: return "审核不通过";
                case 3: return "无需审核";
            }
            return "";
        }

        //转换兑换类型
        private static string ConvertProductType(string pt)
        {
            switch (pt)
            {
                case "alipay":return "支付宝";
                case "qb": return "Q币";
                case "coupon": return "优惠券";
                case "object": return "实物商品";
                case "phonebill": return "话费";
                case "phoneflow": return "流量";
                case "virtual": return "虚拟商品";
                case "turntable": return "大转盘";
                case "singleLottery": return "单品抽奖";
                case "hdtoolLottery": return "活动抽奖";
                case "manualLottery": return "手动开奖";
                case "gameLottery": return "游戏";
            }
            return "";
        }

        //导出excel，返回路径
        private static void DataTableExcel(DataTable dtData, String FileName, out string path)
        {
            string sitePath = "Document/";
            string FilePathName = ProductsExchangeManager.GetMapPath(sitePath);
            DirectoryInfo info = Directory.CreateDirectory(FilePathName);
            FilePathName = FilePathName + FileName;

            using (FileStream fs = new FileStream(FilePathName, FileMode.Create))
            {
                byte[] arrWriteData = Encoding.UTF8.GetBytes(GetDataTableHtml(dtData));
                fs.Write(arrWriteData, 0, arrWriteData.Length);
            }
            
            path = "/" + sitePath + FileName;
        }

        private static string GetDataTableHtml(DataTable grid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>");
            sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
            sb.Append("<tr><td>兑吧订单号</td><td>商品名称</td><td>消耗积分</td><td>订单状态</td><td>商品类型</td><td>订单时间</td><td>经纪人姓名</td><td>经纪人电话</td><td>城市</td><td>管理员姓名</td><td>管理员电话</td><td>审核状态</td>");
            if (grid.Rows.Count > 0)
            {
                foreach (DataRow row in grid.Rows)
                {
                    sb.Append(@"<tr><td>" + (string.IsNullOrWhiteSpace(row["DuibaOrderNum"].ToString()) ? "" : "|"+row["DuibaOrderNum"].ToString()) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["Description"].ToString()) ? "" : row["Description"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["Credits"].ToString()) ? "" : row["Credits"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["State"].ToString()) ? "" : row["State"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["ProductType"].ToString()) ? "" : row["ProductType"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["CreateTime"].ToString()) ? "" : row["CreateTime"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["UserName"].ToString()) ? "" : row["UserName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["Phone"].ToString()) ? "" : row["Phone"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["CityName"].ToString()) ? "" : row["CityName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["AdminName"].ToString()) ? "" : row["AdminName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["AdminPhone"].ToString()) ? "" : row["AdminPhone"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["AuditStatus"].ToString()) ? "" : row["AuditStatus"])  +
                         "</td></tr>");
                }
            }
            else
            {
                sb.Append(@"<tr><td colspan='5' style='text-align:center'>未能找到相关信息</td></tr>");
            }
            sb.Append("</table>");

            return sb.ToString();
        }

        #endregion
        private static DbExecuter GetListSql(DbTour db, string orderNum, int state, int city, string type,DateTime start,DateTime end,string phone)
        {
            string sql = @"SELECT DuibaOrderNum,Description,Credits,Other,a.State,AuditStatus,ProductType,CreateTime,F_Title as UserName,F_Name as CityName,F_Phone as Phone,F_RealName as AdminName,F_CellPhone as AdminPhone FROM duibaorder a 
                LEFT JOIN t_oauthinfo b ON a.UserToken=b.F_UserToken 
                LEFT JOIN t_broker c ON b.F_BrokerKid=c.Kid 
                LEFT JOIN s_admin d ON b.F_AdminKid=d.Kid 
                LEFT JOIN t_provincecityarea e ON e.Kid=c.F_CityKid Where 1=1";

            if (!string.IsNullOrEmpty(orderNum))
            {
                sql += " And a.DuibaOrderNum Like {0:OrderNum}";
            }
            if (state >= 0 && state <= 3)
            {
                sql += " And a.AuditStatus={0:AuditState}";
            }
            if (city > 0)
            {
                if (city == 112)
                    sql += " And (ISNULL(c.F_CityKid) OR C.F_CityKid={0:City})";
                else
                    sql += " And c.F_CityKid={0:City}";

            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                sql += " And a.ProductType={0:ProductType}";
            }
            if(start!=DateTime.MinValue)
            {
                sql += " And a.CreateTime>={0:Start}";
            }
            if(end!=DateTime.MinValue)
            {
                end = end.AddDays(1).AddSeconds(-1);
                sql += " And a.CreateTime<={0:End}";
            }
            if(!string.IsNullOrEmpty(phone))
            {
                sql += " And (c.F_Phone={0:Phone} OR d.F_CellPhone={0:Phone})";
            }
            return db.Sql(sql, new { OrderNum = "%" + orderNum + "%", AuditState = state, City = city, ProductType = type, Start = start, End = end, Phone = phone });
        }
        public static async Task<int> RechargePoint(string userToken,string pointCode,int productExchangeKid)
        {
            T_PointMain_new pointMain = new T_PointMain_new();
            bool isExist = MemoryTable.TB_PointMain_new.ContainsKey(userToken.ToUpper());
            if (isExist)
            {
                pointMain = MemoryTable.TB_PointMain_new[userToken.ToUpper()];
            }
            else
            {
                pointMain = await PointManager.Get(userToken);
            }
            if (pointMain == null) return -1;//用户不存在
            var rule = await PointRuleManager.GetRuleByCode(pointCode);
            if (rule == null)
                return -2;//积分规则不存在或未被启用
            using (var db = DataBaseContext.GetDataBase("default"))
            {
                return await PointManager.UpdateUserPoint(userToken, rule.F_Point, rule.F_PointCode, productExchangeKid, "ADD", db);
            }
        }
    }

}
